#!/usr/bin/env python

'''
This module implements classes for working with the frame database.
'''

import sqlite3
import time
import datetime
import Image

class Frame(object):
    '''
    Implements a single frame/image to be added to the database or returned
    from it.
    '''
    
    def __init__(self, device, filename, label=None, timestamp=None, 
        motion=False):
        '''
        Initializes a new video frame that can be inserted into the Frame
        Database.
        '''
        self.device = device
        self.filename = filename
        self.label = None
        if timestamp is None: self.timestamp = time.time()
        else: self.timestamp = timestamp
        self.motion = motion
    
    def get_image(self):
        '''
        Load the frame file and return the PIL Image object.
        '''
        return Image.open(self.filename)
    
    def _insert_statement(self, table='frames'):
        '''
        Gets the SQL code and parameters required to insert this frame into
        the database.
        '''
        sql = '''INSERT INTO %s (timestamp, device, label,
            motion_detected, filename) VALUES (?,?,?,?,?);''' % table
        params = (self.timestamp, self.device, self.label, self.motion, 
            self.filename)
        return sql, params
            
class FrameDatabase(object):
    '''
    Manages the frame database by adding new frames and searching for frames.
    
    Todo: 
        - fix the threading issue with sqlite3, revert temporary fix which
          opens a new connection each call.
        - add searching/listing function
        - use a row factory to return a dict
    '''
    
    def _get_fn(self): return self._fn
    def _set_fn(self, value): self._fn = value
    filename = property(_get_fn, _set_fn, 
        'Gets/sets the active database by filename.')
        
    def __init__(self, fn):
        '''
        Class initializer takes the database filename as its only argument.
        '''
        self._fn = fn
        conn = sqlite3.connect(self._fn, isolation_level=None)
        try:
            conn.execute('''CREATE TABLE frames (
                timestamp FLOAT PRIMARY KEY,
                device TEXT,
                label TEXT,
                motion_detected BOOLEAN,
                filename TEXT);''')
        except sqlite3.OperationalError: pass
        finally: conn.close()


    def add_frame(self, frame):
        '''
        Inserts a new frame row into the database.
        '''
        sql, params = frame._insert_statement()
        conn = sqlite3.connect(self._fn, isolation_level=None)
        try:
            conn.execute(sql, params)
        finally: conn.close()

#-------------------------------------------------------------------------------
# test script
if __name__ == '__main__':
    
    conn = sqlite3.connect('/tmp/opencircuit/frames.db')
    c = conn.cursor()
    c.execute('SELECT COUNT(*) FROM frames;')
    row = c.fetchone()
    print 'There are %d frames in the database.' % int(row[0])
    c.close()
    conn.close()


